<?php

namespace app\admin\controller\zhongzhuan\ec;

use app\common\controller\Backend;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;





/**
 * 
 *
 * @icon fa fa-circle-o
 */
class Tongji extends Backend
{

    // 默认宽度
    private $defaultWidth = 16;
    // 默认导出格式
    private $exportType = '.xlsx';
    // 表头最大层级
    private $maxHeight  = 1;
    /**
     * Tongji模型对象
     * @var \app\admin\model\zhongzhuan\ec\Tongji
     */
    protected $model = null;
    
    protected $relationSearch = true;
    

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\zhongzhuan\ec\Tongji;

    }



    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */
    
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        $ids = $this->request->param('ids',0);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            

            list($where, $sort, $order, $offset, $limit) = $this->buildparams();

            $list = $this->model->field('*,(by_assign + fy_assign) assign_count')
                ->with([
                    'userinfo'=>function($query){
                        $query->withField('userid,name,account,mobile,deptId');
                    }

                ])
                ->where($where)
                ->order($sort, $order)
                ->paginate($limit)
                ->each(function($item){                    
                    $item['date'] = date("Y-m-d",$item['createtime']);
                    $item['deptinfo'] = db('ec_department')->where('deptId',$item['userinfo']['deptId'])->find();
                    return $item;
                });
              
     


            $result = array("total" => $list->total(), "rows" => $list->items());

            return json($result);
        }        
        return $this->view->fetch();
    }
    
    
    public function export()
    {
        set_time_limit(0);//设置时间限制
        $ids = $this->request->post('ids'); //获取数据的所有id
        $header = [
            ['title'=> '日期'], 
            ['title'=> '所属部门'], 
            ['title'=> '员工'], 
            ['title'=> '百应','children'=>[
                ['title'=> '自动分配'], ['title'=> '手动分配'], ['title'=> '合计分配'],  
            ]], 
            ['title'=> '飞鱼','children'=>[
                ['title'=> '自动分配'], ['title'=> '手动分配'], ['title'=> '合计分配'],  
            ]], 
            ['title'=> '合计'], 
        ];
        
        
        //导出的字段
        $fields = [
            ['field'=>'createtime','type'=>'varchar','name'=> '日期'], 
            ['field'=>'deptinfo>deptName','type'=>'varchar','name'=> '所属部门'], 
            ['field'=>'name','type'=>'varchar','name'=> '员工'],             
            ['field'=>'by_auto_assign','type'=>'varchar','name'=> '自动分配'], 
            ['field'=>'by_shoudong_assign','type'=>'varchar','name'=> '手动分配'],  
            ['field'=>'by_assign','type'=>'varchar','name'=> '合计分配'], 
            ['field'=>'fy_auto_assign','type'=>'varchar','name'=> '自动分配'], 
            ['field'=>'fy_shoudong_assign','type'=>'varchar','name'=> '手动分配'], 
            ['field'=>'fy_assign','type'=>'varchar','name'=> '合计分配'], 
            ['field'=>'assign_count','type'=>'varchar','name'=> '合计'], 
        ];

        foreach ($fields as $key=>&$value){
            $value['char'] =  $this->getColumn($key);
        }
        
      

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        
        $colManage = $this->setHeaderNeedManage($header);
        // 完善单元格合并信息
        $colManage = $this->completeColMerge($colManage);
        // 合并单元格
        $sheet = $this->queryMergeColumn($colManage,$sheet,'');
        
        
        
//            halt($fields);

        //查询数据
        $request_data = $this->request->param();
        $where = null;
        $filter = empty($request_data['filter']) ? [] : json_decode($request_data['filter'],true);
        if(!empty($filter['createtime'])){
            $time_info = explode(' - ', $filter['createtime']);
            $starttime = strtotime($time_info[0]);
            $endtime = strtotime($time_info[1]);
            
            $where['a.createtime']=['between',[$starttime,$endtime]];
        }
       
        if(!empty($filter['ecuser_id'])){
            $where['a.ecuser_id']=$filter['ecuser_id'];
        }
        if(!empty($filter['userinfo.deptId'])){
            $where['u.deptId']=$filter['userinfo.deptId'];
        }
        
        
        $wheres = null;
        if ($ids != 'all' && !empty($ids)) {
            $wheres['a.id'] = ['in', $ids];
        }
        $list = db('ec_tongji')->alias('a')->field('a.*,(by_assign + fy_assign) assign_count,u.userid,u.name,u.account,u.mobile,u.deptId')
                ->join('ec_user u','a.ecuser_id = u.id')
            ->where($where) 
            ->where($wheres)           
            ->select();
        foreach ($list as &$item){
            $item['date'] = date("Y-m-d",$item['createtime']);
            $item['deptinfo'] = db('ec_department')->where('deptId',$item['deptId'])->find();
        }
        
       
        
        //输出表格
        foreach ($list as $key => &$val) {
            
            $i = $key + 3;//表格是从2开始的,第1行为表头
            foreach ($fields as &$field) {
                $data = $val[$field['field']] ?? '';
                //连表显示的字段
                if (strpos($field['field'], '>') !== false) {
                    $fieldArr = explode('>', $field['field']);
                    $data = empty($val[$fieldArr[0]][$fieldArr[1]]) ? '' : $val[$fieldArr[0]][$fieldArr[1]];
                }
                //转换为时间格式
                if (is_numeric($data) && mb_strlen($data) == 10) {
                    $data = date('Y-m-d', $data);
                }
                //将选择项转换成对应的值
                if (!empty($field['chose'])) {
                    $data = $field['chose'][$data];
                }
                //将char和varchar数据类型的单元格设置为文本类型，以防出现科学计数法
                if (strpos($field['type'], 'char') !== false) {
                    $sheet->setCellValueExplicit($field['char'] . $i, $data, 's');
                } else {
                    $sheet->setCellValue($field['char'] . $i, $data);
                }
            }
        }

        $outputFileName = date("YmdHis") . '导出.xls';

        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $outputFileName . '"');
        header("Content-Transfer-Encoding: binary");
        header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $objWriter->save('php://output');
        exit;
    }
    
    
    /**
     * 组装单元格合并需要的信息
     * @param $header
     * @param int $cursor
     * @param int $col
     * @param array $colManage
     * @param $parentList
     * @param $parent
     * @throws \Exception
     * @return array
     */
    private function setHeaderNeedManage($header, $col = 1, &$cursor = 0, &$colManage = [], $parent = null, $parentList = [])
{
        
        foreach ($header as $head) {
            if (empty($head['title'])) {
                throw new \Exception('表头数据格式有误');
            }

            if (is_null($parent)) {
                // 循环初始化
                $parentList = [];
                $col = 1;
            } else {
                // 递归进入，高度和父级集合通过相同父级条件从已有数组中获取，避免递归增加与实际数据不符
                foreach ($colManage as $value) {
                    if ($value['parent'] == $parent) {
                        $parentList = $value['parentList'];
                        $col = $value['height'];
                        break;
                    }
                }
            }

            // 单元格标识
            $column = $this->getColumn($cursor) . $col;

            // 组装单元格需要的各种信息
            $colManage[$column] = [
                'title'      => $head['title'],      // 标题
                'cursor'     => $cursor,             // 游标
                'cursorEnd'  => $cursor,             // 结束游标
                'height'     => $col,                // 高度
                'width'      => $this->defaultWidth, // 宽度
                'mergeStart' => $column,             // 合并开始标识
                'hMergeEnd'  => $column,             // 横向合并结束标识
                'zMergeEnd'  => $column,             // 纵向合并结束标识
                'parent'     => $parent,             // 父级标识
                'parentList' => $parentList,         // 父级集合
            ];

            if (isset($head['children']) && !empty($head['children']) && is_array($head['children'])) {
                // 有下级，高度加一
                $col += 1;
                // 当前标识加入父级集合
                $parentList[] = $column;

                $this->setHeaderNeedManage($head['children'], $col, $cursor,$colManage, $column, $parentList);
            } else {
                // 没有下级，游标加一
                $cursor += 1;
            }
        }

        return $colManage;
    }
    
    
    
    /**
     * 完善单元格合并信息
     * @param $colManage
     * @return array
     */
    private function completeColMerge($colManage)
{
        $this->maxHeight = max(array_column($colManage, 'height'));
        $parentManage    = array_column($colManage, 'parent');

        foreach ($colManage as $index => $value) {
            // 设置横向合并结束范围：存在父级集合，把所有父级的横向合并结束范围设置为当前单元格
            if (!is_null($value['parent']) && !empty($value['parentList'])) {
                foreach ($value['parentList'] as $parent) {
                    $colManage[$parent]['hMergeEnd'] = $this->getColumn($value['cursor']) . $colManage[$parent]['height'];
                    $colManage[$parent]['cursorEnd'] = $value['cursor'];
                }
            }

            // 设置纵向合并结束范围：当前高度小于最大高度 且 不存在以当前单元格标识作为父级的项
            $checkChildren = array_search($index, $parentManage);
            if ($value['height'] < $this->maxHeight && !$checkChildren) {
                $colManage[$index]['zMergeEnd'] = $this->getColumn($value['cursor']) . $this->maxHeight;
            }
        }

        return $colManage;
    }
    
    
    /**
     * 合并单元格
     * @param $colManage
     * @param $filter
     */
    private function queryMergeColumn($colManage,$sheet, $filter)
{
        foreach ($colManage as $value) {
            $sheet->mergeCells("{$value['mergeStart']}:{$value['zMergeEnd']}");
            $sheet->getStyle("{$value['mergeStart']}:{$value['zMergeEnd']}")->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $sheet->setCellValue("{$value['mergeStart']}", $value['title']);
            
            $sheet->mergeCells("{$value['mergeStart']}:{$value['hMergeEnd']}");
            $sheet->getStyle("{$value['mergeStart']}:{$value['hMergeEnd']}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->setCellValue("{$value['mergeStart']}", $value['title']);

            // 设置单元格需要的宽度
            if ($value['cursor'] != $value['cursorEnd']) {
                $value['width'] = ($value['cursorEnd'] - $value['cursor'] + 1) * $this->defaultWidth;
            }

//            // 设置列单元格样式
//            $toColumnStart = $this->getColumn($value['cursor']);
//            $toColumnEnd   = $this->getColumn($value['cursorEnd']);
////            getColumnDimension
//            $sheet->getColumnDimension("{$toColumnStart}:{$toColumnEnd}")->setWidth($value['width']);
        }
        return $sheet;

        // 是否开启过滤选项
//        if ($filter) {
//            // 获取最后的单元格标识
//            $filterEndColumn = $this->getColumn(end($colManage)['cursorEnd']) . $this->maxHeight;
//            $sheet->autoFilter("A1:{$filterEndColumn}");
//        }
    }
    
    
    /**
     * 获取单元格列标识
     * @param $num
     * @return string
     */
    private function getColumn($num){
        
        $firstChar='';
        if($num > 25){
            $firstChar = 'A';            
            $secondChar = chr($num - 26 + 65);
        }else{
            $secondChar = chr($num + 65);
        }

        
        return $firstChar . $secondChar;
    }
    
    


}
